package cn.zyy.dao;

import cn.zyy.entity.Score;
import cn.zyy.utils.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * 成绩持久化层
 */
public class ScoreDaoImpl implements ScoreDao {

    @Override
    //根据学生姓名、科目名称和分数给学生记录成绩
    public Integer addScore(long stuId, long courseId, Double score) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        Score sco = selectScore(stuId, courseId);
        String sql = "";
        if (sco == null) {
            sql = "insert into score(score, stu_id,course_id) values(?,?,?)";
        }else {
            sql = "update score set score = ? where stu_id = ? and course_id = ?";
        }
        try {
            int line = qrn.update(sql, score, stuId, courseId);
            return line;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    //查询该表是否有该学生的该科目成绩
    public Score selectScore(long stuId, long courseId) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql = "select * from score where stu_id = ? and course_id = ?";
        Score score = null;
        try {
            score = qrn.query(sql, new BeanHandler<>(Score.class), stuId, courseId);
            return score;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return score;
    }
    @Override
    //查询指定科目的所有学生的成绩
    public List<Score> findAllScores(String courseName) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner qrn = new QueryRunner(dataSource);
        String sql =  "SELECT s.stu_name as stuName , c.course_name as courseName, scr.score  " +
                "FROM score scr LEFT JOIN  student s ON scr.stu_id = s.stu_id" +
                " LEFT JOIN course c ON scr.course_id = c.course_id " +
                "WHERE c.course_name = ? " +
                "ORDER BY scr.score DESC";
        try {
            List<Score> scores = qrn.query(sql, new BeanListHandler<>(Score.class), courseName);
            return scores;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
